import pandas as pd
import numpy as np
df = pd.read_excel('女装销售数据.xlsx')
pd.set_option('display.unicode.east_asian_width', True)

df['最近消费时间间隔'] = (pd.to_datetime(df['下单日期'].max()) - pd.to_datetime(df['下单日期']))/np.timedelta64(1, 'D')
df['历史总订单数（单）'] = df['历史总订单数（单）'].apply(lambda x: x if type(x) != str else x.replace('+', '')).astype('int')
df_temp = df[['买家昵称', '最近消费时间间隔', '历史总订单数（单）', '总交易金额（元）']]
df_temp = df_temp.rename(columns={'最近消费时间间隔': 'R-最近消费时间间隔', '历史总订单数（单）': 'F-消费频率', '总交易金额（元）': 'M-消费金额'})
df_temp = df_temp.set_index(['买家昵称'])
print(df_temp)

df_std = (df_temp - df_temp.mean(axis=0))/df_temp.std(axis=0)
print(df_std)

from sklearn import cluster
k = 4
kmodel = cluster.KMeans(n_clusters=k)
kmodel.fit(df_std)
df_temp['客户类别'] = kmodel.labels_
print(df_temp)

df_mean = df_temp.groupby('客户类别').mean()
df_mean['客户数'] = df_temp.groupby('客户类别').size()
df_mean.loc['判定值'] = df_mean.mean()
print(df_mean)